package com.F03.action;

import java.util.ArrayList;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.log4j.Logger;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import com.F03.actionform.F0305ActionForm;
import com.F03.dao.PdFinishProductDAO;
import com.F03.info.PdPlanningInfo;
import com.action.BaseAction;
import com.config.Constants;
import com.info.T_S_YHCZXXinfo;
import com.util.CommonTools;
import com.util.ExcelExport;

/**
 * 
 * @file F0903Action.java
 * @author huqy
 * @since 2009-3-15
 * @version 1.0
 */
public class F0305Action extends BaseAction {

	/**
	 * Method execute
	 * @param mapping
	 * @param form
	 * @param request
	 * @param response
	 * @return ActionForward
	 */
	Logger log = Logger.getLogger(F0305Action.class);

	/** 操作记录INFO */
	T_S_YHCZXXinfo t_YhczxxInfo = null;
	
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response){
		F0305ActionForm actionForm = (F0305ActionForm) form;
		PdFinishProductDAO dao = new PdFinishProductDAO();
		PdPlanningInfo info = new PdPlanningInfo();
		String operationType = actionForm.getOperationType();
		String strList = "list";
		
		try {
			//操作记录
			t_YhczxxInfo = setYhczxx(request, response, operationType,"F0305");

			/*
			if(Constants.BACK.equals(operationType)){
				return mapping.findForward(strList);
			}
			*/

			if(Constants.QUERYLIST.equals(operationType)){
				info.setManufacturingOrder1(CommonTools.null2String(actionForm.getManufacturingOrder1()));
				info.setManufacturingOrder2(CommonTools.null2String(actionForm.getManufacturingOrder2()));
				info.setLastAdvanceDate1(CommonTools.null2String(actionForm.getLastAdvanceDate1()));
				info.setLastAdvanceDate2(CommonTools.null2String(actionForm.getLastAdvanceDate2()));
				doQueryList(actionForm,dao.queryFinishProductList(info),dao.queryTotalList(info));
				//actionForm.setList(dao.queryFinishProductList(info));
				//actionForm.setTotalList(dao.queryTotalList(info));
				return mapping.findForward(strList);
			}
			
			else if(Constants.INIT.equals(operationType)){
				return mapping.findForward(strList);
			}
			else if("export".equals(operationType)){
				doExport(actionForm, dao, response);
				return mapping.findForward("list");
			}
		} catch (Exception e){
			e.printStackTrace();
		} finally {
			if (dao != null) dao.disConnect();
		}
		return mapping.findForward(strList);
	}
	
	private void doQueryList(F0305ActionForm actionForm,List list,List totalList){
		if(list!=null&&list.size()>0){
			actionForm.setList(list);
			actionForm.setTotalList(totalList);
		}
		else{
			actionForm.setList(list);
			actionForm.setTotalList(totalList);
			actionForm.setErrorFlag("2");
		}	
	}
	
	private void doExport(F0305ActionForm actionForm, PdFinishProductDAO dao, HttpServletResponse response){
		List titleList = new ArrayList();
		try {
			String fileName = "完成产品报告";
			/* 标题设定 */
			titleList.add("工作号");
			titleList.add("产品模型");
			titleList.add("总数量");
			titleList.add("组");
			titleList.add("数量");
			titleList.add("完成时间");
			titleList.add("销售单价(rmb)");
			titleList.add("总金额(rmb)");
			
			/* sql作成 */
			String sql = getSql(actionForm);
			String sqlTotal = getSqlTotal(actionForm);
			/* excel输出 */
			
			ExcelExport.exportTotal(fileName, titleList, sql,sqlTotal, response);

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (dao != null)
				dao.disConnect();
		}
	}

	public String getSql(F0305ActionForm actionForm){
		StringBuffer sql=new StringBuffer();
		sql.append(" select a.manufacturing_order,b.name,adyh.FUN_GETFINISHQTY(a.manufacturing_order) as finish_qty, ");
    	sql.append(" a.groups,a.qty,convert(varchar(10),a.stock_date,120) as stock_date,d.unit_price, ");
    	sql.append(" (d.unit_price*a.qty) as total_price ");
    	sql.append(" from pd_finish_product a, t_product_model b,t_not_standard_products c,c_orders d ");
    	sql.append("  where a.manufacturing_order = c.manufacturing_order and  ");
    	sql.append(" c.model_type = b.code and a.manufacturing_order = d.manufacturing_order ");
    	if(!actionForm.getManufacturingOrder1().equals("")){
    		sql.append(" and a.manufacturing_order >='"+actionForm.getManufacturingOrder1()+"' ");
    	}
    	if(!actionForm.getManufacturingOrder2().equals("")){
    		 sql.append(" and a.manufacturing_order <='"+actionForm.getManufacturingOrder2()+"' ");
    	}
    	if(!actionForm.getLastAdvanceDate1().equals("")){
    		sql.append(" and a.stock_date >=convert(datetime,'"+actionForm.getLastAdvanceDate1()+"')");
    	}
    	if(!actionForm.getLastAdvanceDate2().equals("")){
    		sql.append(" and a.stock_date <=convert(datetime,'"+actionForm.getLastAdvanceDate2()+"')");
    	}
        sql.append(" order by a.manufacturing_order ");
		return sql.toString();
	}
	
	public String getSqlTotal(F0305ActionForm actionForm){
		StringBuffer sql=new StringBuffer();
		sql.append(" select '总计','',sum(a.qty) as total_group_qty,'',sum(a.qty) as group_qty,'',sum(convert(float,d.unit_price)) as total_unit_price,sum(d.unit_price*a.qty) as total_total_price ");
    	sql.append(" from pd_finish_product a,c_orders d ");	
    	sql.append(" where  a.manufacturing_order = d.manufacturing_order ");
    	
    	if(!actionForm.getManufacturingOrder1().equals("")){
    		sql.append(" and a.manufacturing_order >='"+actionForm.getManufacturingOrder1()+"' ");
    	}
    	if(!actionForm.getManufacturingOrder2().equals("")){
    		 sql.append(" and a.manufacturing_order <='"+actionForm.getManufacturingOrder2()+"' ");
    	}
    	if(!actionForm.getLastAdvanceDate1().equals("")){
    		sql.append(" and a.stock_date >=convert(datetime,'"+actionForm.getLastAdvanceDate1()+"')");
    	}
    	if(!actionForm.getLastAdvanceDate2().equals("")){
    		sql.append(" and a.stock_date <=convert(datetime,'"+actionForm.getLastAdvanceDate2()+"')");
    	}
		return sql.toString();
	}
}
